﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using ZHDJ.Core;
using ZHDJ.Core.Util;

namespace ZHDJ.Data.BLL
{
    /// <summary>
    /// 积分
    /// </summary>
    public class PartyScoreHelper
    {
        #region 增加积分

        #region 增加在线学习积分
        /// <summary>
        /// 增加课件点击积分
        /// </summary>
        /// <param name="objectId">登陆人员</param>
        /// <returns></returns>
        public static string AddOnlineLearning(string objectId, string coursewarename)
        {
            DateTime dt = DateTime.Now;
            var scoreInfo = new ScoreInfo()
            {
                ObjectID = objectId,
                CreateBy = objectId,
                Category = SysEnum.PartyScoreCategory.OnlineLearning.ToString(),
                ObjectType = SysEnum.PartyScoreObjectType.Member,
                Description = string.Format("{0}观看课件{1}，达到规定时间", dt.ToString("yyyy-MM-dd HH:mm:ss"), coursewarename)
            };
            return Add(scoreInfo);
        }
        #endregion

        #region 增加课件点击积分
        /// <summary>
        /// 增加课件点击积分
        /// </summary>
        /// <param name="objectId">登陆人员</param>
        /// <returns></returns>
        public static string AddCourseWareClick(string objectId, string coursewarename)
        {
            DateTime dt = DateTime.Now;
            var scoreInfo = new ScoreInfo()
            {
                ObjectID = objectId,
                CreateBy = objectId,
                Category = SysEnum.PartyScoreCategory.CourseWareClick.ToString(),
                ObjectType = SysEnum.PartyScoreObjectType.Member,
                Description = string.Format("{0}点击了课件{1}", dt.ToString("yyyy-MM-dd HH:mm:ss"), coursewarename)
            };
            return Add(scoreInfo);
        }
        #endregion

        #region 增加每天登陆积分
        /// <summary>
        /// 增加每天登陆积分
        /// </summary>
        /// <param name="objectId">登陆人员</param>
        /// <returns></returns>
        public static string AddDayLogin(string objectId)
        {
            DateTime dt = DateTime.Now;
            var scoreInfo = new ScoreInfo()
            {
                ObjectID = objectId,
                CreateBy = objectId,
                Category = SysEnum.PartyScoreCategory.DayLogin.ToString(),
                ObjectType = SysEnum.PartyScoreObjectType.Member,
                Description = string.Format("{0}登陆网站", dt.ToString("yyyy-MM-dd"))
            };
            return Add(scoreInfo);
        }
        #endregion

        #region 增加党费缴纳积分
        /// <summary>
        /// 增加党费缴纳积分
        /// </summary>
        /// <param name="objectId">获取的用户</param>
        /// <param name="createBy">创建人</param>
        /// <param name="foreignId">关联ID</param>
        /// <param name="scoreDate">积分时间(为空时为当前时间)</param>
        /// <returns></returns>
        public static string AddMemberCost(string objectId, string createBy, string foreignId, string scoreDate = "")
        {
            var dt = ToScoreDate(scoreDate);
            var description = string.Format("{0}年{1}月党费缴纳", dt.Year, dt.Month);
            return AddForForeignId(objectId, createBy, foreignId, scoreDate, description,
                SysEnum.PartyScoreCategory.MemberCost.ToString(), SysEnum.PartyScoreObjectType.Member);
        }
        #endregion

        #region 增加心得体会积分
        /// <summary>
        /// 增加心得体会积分
        /// </summary>
        /// <param name="objectId">获取的用户</param>
        /// <param name="createBy">创建人</param>
        /// <param name="foreignId">关联ID</param>
        /// <param name="scoreDate">积分时间(为空时为当前时间)</param>
        /// <returns></returns>
        public static string AddLessonsLearned(string objectId, string createBy, string foreignId, string scoreDate = "")
        {
            var dt = ToScoreDate(scoreDate);
            var description = string.Format("{0}提交一篇心得体会", dt.ToString("yyyy-MM-dd"));
            return AddForForeignId(objectId, createBy, foreignId, scoreDate, description,
                SysEnum.PartyScoreCategory.LessonsLearned.ToString(), SysEnum.PartyScoreObjectType.Member);
        }
        #endregion
        #region 增加思想汇报积分
        /// <summary>
        /// 增加心得体会积分
        /// </summary>
        /// <param name="objectId">获取的用户</param>
        /// <param name="createBy">创建人</param>
        /// <param name="foreignId">关联ID</param>
        /// <param name="scoreDate">积分时间(为空时为当前时间)</param>
        /// <returns></returns>
        public static string AddThoughtRepor(string objectId, string createBy, string foreignId, string scoreDate = "")
        {
            var dt = ToScoreDate(scoreDate);
            var description = string.Format("{0}提交一篇思想汇报", dt.ToString("yyyy-MM-dd"));
            return AddForForeignId(objectId, createBy, foreignId, scoreDate, description,
                SysEnum.PartyScoreCategory.ThoughtRepor.ToString(), SysEnum.PartyScoreObjectType.Member);
        }
        #endregion

        #region 增加学习测试积分
        /// <summary>
        /// 增加学习测试积分
        /// </summary>
        /// <param name="objectId">获取的用户</param>
        /// <param name="foreignId">关联ID</param>
        /// <param name="scoreDate">积分时间(为空时为当前时间)</param>
        /// <returns></returns>
        public static string AddEduTest(string specialFlag, string objectId, string foreignId, string scoreDate = "")
        {
            var name = "";
            using (var DB = new DBInstance().DB)
            {
                var temp = DB.PartyEduSpecials.Where(a => a.SpecialFlag == specialFlag);
                if (temp.Any())
                {
                    name = temp.ToList()[0].SpecialName;
                }
            }
            var dt = ToScoreDate(scoreDate);
            var scoreInfo = new ScoreInfo()
            {
                ObjectID = objectId,
                CreateBy = objectId,
                Category = specialFlag,
                ForeignID = foreignId,
                ObjectType = SysEnum.PartyScoreObjectType.Member,
                Description = string.Format("{0}提交学习测试-{1}", dt.ToString("yyyy-MM-dd HH:mm"), name)
            };
            return Add(scoreInfo);

        }
        #endregion

        #region 增加支部活动积分
        /// <summary>
        /// 增加支部活动积分
        /// </summary> 
        /// <param name="loginIds"></param>
        /// <param name="branchId"></param>
        /// <param name="createBy">创建人</param>
        /// <param name="foreignId">关联ID</param>
        /// <param name="scoreDate">积分时间(为空时为当前时间)</param>

        /// <returns></returns>
        public static string AddBranchActivity(List<string> loginIds, string branchId, string createBy, string foreignId,
            string scoreDate = "")
        {


            var dt = ToScoreDate(scoreDate);
            var description = string.Format("{0}参加支部活动", dt.ToString("yyyy-MM-dd"));
            foreach (var item in loginIds)
            {

                AddForForeignId(item, createBy, foreignId, scoreDate, description,
                    SysEnum.PartyScoreCategory.BranchActivity.ToString(), SysEnum.PartyScoreObjectType.Member);

            }
            AddForForeignId(branchId, createBy, foreignId, scoreDate, description,
                    SysEnum.PartyScoreCategory.BranchActivityToBranch.ToString(), SysEnum.PartyScoreObjectType.Branch);
            return "";
        }

        #endregion

        #region 增加三会一课积分
        /// <summary>
        /// 增加三会一课积分
        /// </summary> 
        /// <param name="loginIds"></param>
        /// <param name="branchId"></param>
        /// <param name="createBy">创建人</param>
        /// <param name="foreignId">关联ID</param>
        /// <param name="scoreDate">积分时间(为空时为当前时间)</param>

        /// <returns></returns>
        public static string AddMeetingLecture(List<string> loginIds, string branchId, string createBy, string foreignId,
            string scoreDate = "")
        {
            var dt = ToScoreDate(scoreDate);
            var description = string.Format("{0}参加三会一课", dt.ToString("yyyy-MM-dd"));
            foreach (var item in loginIds)
            {

                AddForForeignId(item, createBy, foreignId, scoreDate, description,
                    SysEnum.PartyScoreCategory.MeetingLecture.ToString(), SysEnum.PartyScoreObjectType.Member);

            }
            AddForForeignId(branchId, createBy, foreignId, scoreDate, description,
                    SysEnum.PartyScoreCategory.MeetingLectureToBranch.ToString(), SysEnum.PartyScoreObjectType.Branch);
            return "";
        }
        #endregion

        #region 增加志愿活动积分
        /// <summary>
        /// 增加志愿活动积分
        /// </summary> 
        /// <param name="loginIds"></param>
        /// <param name="branchId"></param>
        /// <param name="createBy">创建人</param>
        /// <param name="foreignId">关联ID</param>
        /// <param name="scoreDate">积分时间(为空时为当前时间)</param>

        /// <returns></returns>
        public static string AddVolunteerActivity(List<string> loginIds, string branchId, string createBy, string foreignId,
            string scoreDate = "")
        {
            var dt = ToScoreDate(scoreDate);
            var description = string.Format("{0}参加志愿活动", dt.ToString("yyyy-MM-dd"));
            foreach (var item in loginIds)
            {
                AddForForeignId(item, createBy, foreignId, scoreDate, description,
                    SysEnum.PartyScoreCategory.VolunteerActivity.ToString(), SysEnum.PartyScoreObjectType.Member);
            }
            AddForForeignId(branchId, createBy, foreignId, scoreDate, description,
                    SysEnum.PartyScoreCategory.VolunteerActivityToBranch.ToString(), SysEnum.PartyScoreObjectType.Branch);
            return "";
        }

        #endregion

        #region 增加论坛积分

        public static string AddForum(SysEnum.ForumScoreType scoreType, string loginId, string createBy,
            string foreignId, string paraEx = "")
        {

            var para = new List<MySqlParameter>
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = scoreType.ToString()
                },
                new MySqlParameter() {DbType = DbType.String, ParameterName = "CreateBy", Value = createBy},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "ForeignID", Value = foreignId},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "ObjectID", Value = loginId},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "paraEx", Value = paraEx},

            };
            var sql =
                string.Format(@" call sp_ScoreForum(?Category,?ObjectID,?CreateBy,?ForeignID,?paraEx); ");
            MySqlHelper.ExecuteDataSet(sql, para.ToArray());
            return "";

        }

        #endregion

        #region 增加每日一题积分
        /// <summary>
        /// 增加每日一题积分
        /// </summary>
        /// <param name="objectId">获取的用户</param>
        /// <returns></returns>
        public static string AddEverydaylearning(string objectId)
        {
            DateTime dt = DateTime.Now;
            var scoreInfo = new ScoreInfo()
            {
                ObjectID = objectId,
                CreateBy = objectId,
                Category = SysEnum.PartyScoreCategory.EverydayLearning.ToString(),
                ObjectType = SysEnum.PartyScoreObjectType.Member,
                Description = string.Format("{0}每日一学", dt.ToString("yyyy-MM-dd"))
            };
            return Add(scoreInfo);

        }
        #endregion

        /// <summary>
        /// 增加积分(有关联ID)
        /// </summary>
        /// <returns></returns>
        private static string AddForForeignId(string objectId, string createBy, string foreignId, string scoreDate, string description,
            string category, SysEnum.PartyScoreObjectType objectType)
        {
            if (IsExistScore(objectId, category.ToString(), foreignId))
            {
                return "-1";
            }

            var scoreInfo = new ScoreInfo()
            {
                ObjectID = objectId,
                CreateBy = createBy,
                ScoreDate = scoreDate,
                ForeignID = foreignId,
                Category = category,
                ObjectType = objectType,
                Description = description
            };
            return Add(scoreInfo);
        }



        /// <summary>
        /// 增加积分（统一异常处理）
        /// </summary>
        /// <param name="func">增加积分方法</param>
        /// <returns></returns>
        public static string Add(Func<string> func)
        {
            try
            {
                return func();
            }
            catch (Exception ex)
            {

                return "-1";
            }
        }

        /// <summary>
        /// 增加积分
        /// </summary>
        private static string Add(ScoreInfo scoreInfo)
        {
            if (scoreInfo == null)
            {
                return "-1";
            }
            var scoreDate = "0";
            if (ValidateHelper.IsDateTime(scoreInfo.ScoreDate))
            {
                scoreDate = Convert.ToDateTime(scoreInfo.ScoreDate).ToString("yyyy-MM-dd HH:mm:ss");
            }

            var para = new List<MySqlParameter>
            {
                new MySqlParameter() {DbType = DbType.String, ParameterName = "Category", Value = scoreInfo.Category},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "CreateBy", Value = scoreInfo.CreateBy},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "Description",Value = scoreInfo.Description},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "Flag", Value = scoreInfo.Flag},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "ScoreDate", Value = scoreDate},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "ForeignID", Value = scoreInfo.ForeignID},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "ObjectID", Value = scoreInfo.ObjectID},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "ObjectType",Value = scoreInfo.ObjectType.ToString()},

            };
            var sql =
                new StringBuilder(@" call Sp_ScoreAdd(?Category,?ScoreDate,?ObjectID,?ObjectType,?CreateBy,?ForeignID,?Flag,?Description); ");

            var outvalue = MySqlHelper.ExecuteDataTable(sql.ToString(), para.ToArray());
            return Convert.ToString(outvalue.Rows[0][0]);
        }

        /// <summary>
        /// 是否存在积分
        /// </summary>
        /// <param name="objectId">用户ID</param>
        /// <param name="category">积分标识</param>
        /// <param name="foreignId">积分关联id</param>
        /// <returns></returns>
        private static bool IsExistScore(string objectId, string category, string foreignId)
        {
            using (var DB = new DBInstance().DB)
            {
                var score =
                    DB.PartyObjectScores.Count(
                        a => a.ObjectID == objectId && a.Category == category && a.ForeignID == foreignId);
                return score > 0;
            }

        }


        /// <summary>
        /// 转化积分时间
        /// </summary>
        /// <param name="scoreDate"></param>
        /// <returns></returns>
        private static DateTime ToScoreDate(string scoreDate)
        {
            DateTime dt = DateTime.Now;
            if (ValidateHelper.IsDateTime(scoreDate))
            {
                dt = Convert.ToDateTime(scoreDate);
            }
            return dt;
        }
        #endregion


        #region 获取排名
        /// <summary>
        /// 获取党员总排名
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="LoginId"></param>
        /// <returns></returns>
        public static DataSet GetAllRankMember(int pageIndex, int pageSize, string LoginId = "", string userName = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize}
            };
            sb.Append(@" select SQL_CALC_FOUND_ROWS * from tblpartyscorerankmember data
                        where 1=1 ");
            if (!string.IsNullOrEmpty(LoginId))
            {
                sb.Append(" and data.LoginId=?LoginId ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "LoginId", Value = LoginId });
            }

            if (!string.IsNullOrEmpty(userName))
            {
                sb.Append(" and data.userName like ?userName ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "userName", Value = string.Format("%{0}%", userName) });
            }
            sb.Append(" order by Rank  LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS(); ");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }

        /// <summary>
        /// 获取组织总排名
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="branchId"></param>
        /// <returns></returns>
        public static DataSet GetAllRankBranch(int pageIndex, int pageSize, string branchId = "", string branchName = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize}
            };
            sb.Append(@" select SQL_CALC_FOUND_ROWS * from tblpartyscorerankbranch where 1=1 ");
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and BranchID=?BranchID ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            if (!string.IsNullOrEmpty(branchName))
            {
                sb.Append(" and branchName like ?branchName ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "branchName", Value = string.Format("%{0}%", branchName) });
            }
            sb.Append(" order by Rank  LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS(); ");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }

        /// <summary>
        /// 获取组织排名
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="parentBranchId"></param>
        /// <param name="branchType"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetScoreRankBranch(int pageIndex, int pageSize, string parentBranchId, string branchType, string searchKey = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "parentBranchID", Value = parentBranchId},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "branchType", Value = branchType},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "searchKey", Value = searchKey}
            };
            sb.Append(@" call sp_GetScoreRankBranch(?Begin,?PageSize,?parentBranchID,?branchType,?searchKey); ");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }

        /// <summary>
        /// 获取组织排名（测试数据）
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="parentBranchId"></param>
        /// <param name="branchType"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetScoreRankBranchTest(int pageIndex, int pageSize, string parentBranchId, string branchType, string searchKey = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize}
            };
            sb.Append(@" SELECT * FROM tblpartyscorerankbranch ORDER BY Rank LIMIT ?Begin,?PageSize; ");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }

        /// <summary>
        /// 获取人员排名（指定组织）
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="inBranchId"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetScoreRankMemberByBranchId(int pageIndex, int pageSize, string inBranchId, string searchKey = "", string isallmember = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "inBranchID", Value = inBranchId},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "searchKey", Value = searchKey}
            };
            sb.Append(@" call sp_GetScoreRankMemberByBranchID(?Begin,?PageSize,?inBranchID,?searchKey); ");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }

        /// <summary>
        /// 获取人员排名（指定组织）
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="inBranchType"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetScoreRankMemberByBranchType(int pageIndex, int pageSize, string inBranchType, string searchKey = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "inBranchType", Value = inBranchType},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "searchKey", Value = searchKey}
            };
            sb.Append(@" call sp_GetScoreRankMemberByBranchType(?Begin,?PageSize,?inBranchType,?searchKey); ");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }

        /// <summary>
        /// 获取组织生活排名
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="branchId"></param>
        /// <param name="branchName"></param>
        /// <returns></returns>
        public static DataSet GetAllRankBranchActivities(int pageIndex, int pageSize, string branchId = "", string branchName = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize}
            };
            sb.Append(@" select SQL_CALC_FOUND_ROWS * from tblpartyscorerankbranchactivity where 1=1 ");
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and BranchID=?BranchID ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            if (!string.IsNullOrEmpty(branchName))
            {
                sb.Append(" and branchName like ?branchName ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "branchName", Value = string.Format("%{0}%", branchName) });
            }
            sb.Append(" order by Rank  LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS(); ");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }

        /// <summary>
        /// 获取组织学习排名
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="branchId"></param>
        /// <param name="branchName"></param>
        /// <returns></returns>
        public static DataSet GetAllRankBranchMassLearning(int pageIndex, int pageSize, string branchId = "", string branchName = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize}
            };
            sb.Append(@" select SQL_CALC_FOUND_ROWS * from tblpartyscorerankbranchmassedlearning where 1=1 ");
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and BranchID=?BranchID ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            if (!string.IsNullOrEmpty(branchName))
            {
                sb.Append(" and branchName like ?branchName ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "branchName", Value = string.Format("%{0}%", branchName) });
            }
            sb.Append(" order by Rank  LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS(); ");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());

        }
        #endregion

        #region 统计排名
        /// <summary>
        /// 统计排名
        /// </summary>
        /// <returns></returns>
        public static void InitScorcRank()
        {
            var sql = string.Format(@" call sp_ScorcRank(); ");
            MySqlHelper.ExecuteNonQuery(sql);
        }

        public static int QueryPartyBranchCount(string keyword, string branchType = "")
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>();
            sbSql.Append(@"SELECT COUNT(*) FROM tblpartyscorerankbranch  WHERE 1 = 1");
            if (!string.IsNullOrEmpty(keyword))
            {
                sbSql.Append(" AND BranchName LIKE ?BranchName ");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchName", Value = string.Format("%{0}%", keyword) });
            }
            if (!string.IsNullOrEmpty(branchType))
            {
                sbSql.Append(" and BranchType=?BranchType ");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchType", Value = branchType });
            }

            object oCount = MySqlHelper.ExecuteScalar(sbSql.ToString(), list.ToArray());
            if (oCount != null) return Convert.ToInt32(oCount);
            else return 0;
        }
        #endregion

        public static int QueryPartyMemberCount(string keyword, string branchType = "", string branchid = "", string isAllmember = "")
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>();
            sbSql.Append(@"SELECT COUNT(*) FROM tblpartyscorerankmember WHERE 1 =1");
            if (!string.IsNullOrEmpty(keyword))
            {
                sbSql.Append(" AND UserName like ?UserName ");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "UserName", Value = string.Format("%{0}%", keyword) });
            }
            if (!string.IsNullOrEmpty(branchType))
            {
                sbSql.Append(" and BranchType=?BranchType ");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchType", Value = branchType });
            }
            if (!string.IsNullOrEmpty(branchid))
            {
                sbSql.Append(" and branchid=?branchid ");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "branchid", Value = branchid });
            }

            if (!string.IsNullOrEmpty(isAllmember))
            {
                sbSql.Append(" and EXISTS (select * from tblpartyorgmember b where b.runsate=0 and b.loginid= tblpartyscorerankmember.loginid) ");
            }

            object oCount = MySqlHelper.ExecuteScalar(sbSql.ToString(), list.ToArray());
            if (oCount != null) return Convert.ToInt32(oCount);
            else return 0;
        }

        public static int QueryPartyscorerankbranchmassedlearningCount(string keyword, string branchType = "")
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>();
            sbSql.Append(@"SELECT COUNT(*) FROM tblpartyscorerankbranchmassedlearning WHERE 1 =1");
            if (!string.IsNullOrEmpty(keyword))
            {
                sbSql.Append(" and branchName like ?branchName ");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "branchName", Value = string.Format("%{0}%", keyword) });
            }
            if (!string.IsNullOrEmpty(branchType))
            {
                sbSql.Append(" and BranchType=?BranchType ");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchType", Value = branchType });
            }

            object oCount = MySqlHelper.ExecuteScalar(sbSql.ToString(), list.ToArray());
            if (oCount != null) return Convert.ToInt32(oCount);
            else return 0;
        }

        public static DataTable QueryMemberScore(string loginID)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>();
            sbSql.Append(@"select * from tblpartyscorerankmember where LoginID=?LoginID ");
            list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "LoginID", Value = loginID });
            return MySqlHelper.ExecuteDataTable(sbSql.ToString(), list.ToArray());
        }

        public static DataTable QueryBranchScore(string branchID)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>();
            sbSql.Append(@"select * from tblpartyscorerankbranch where BranchID=?BranchID ");
            list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchID });
            return MySqlHelper.ExecuteDataTable(sbSql.ToString(), list.ToArray());
        }

        public static DataTable QueryDayStar()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT usr.LoginID, usr.UserName, usr.ImageUrl AS Photo, 
                                    SUM(CASE WHEN DATEDIFF(now(),ScoreDate)=1 THEN data.Score ELSE 0 END) AS TMP,
                                    SUM(Score) AS Score 
                                    FROM tblPartyObjectScore data
                                    INNER JOIN tblPartyOrgMember usr ON usr.LoginID = data.ObjectID
                                    WHERE ObjectType = 'Member'
                                    GROUP BY usr.LoginID, usr.UserName, usr.ImageUrl
                                    ORDER BY TMP DESC,Score DESC LIMIT 0,1");
            return MySqlHelper.ExecuteDataTable(sb.ToString());
        }

        public static string GetScoreSum(string objectID)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>();
            sbSql.Append(@"SELECT sum(ifnull(Score,0)) FROM tblPartyObjectScore WHERE  ObjectID=?ObjectID");

            list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ObjectID", Value = objectID });
            object obj = MySqlHelper.ExecuteScalar(sbSql.ToString(), list.ToArray());
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "0";
            }
        }

        public static string GetScoreDetail(string objectID, string category, string objectType)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.String, ParameterName = "ObjectID", Value = objectID}
            };

            sbSql.Append(@"SELECT count(*) FROM tblPartyObjectScore score
inner JOIN tblpartyobjectscorerule rule ON rule.Category=score.Category
WHERE ObjectID=?ObjectID");

            if (objectType.Equals("Member"))
            {
                sbSql.Append(" and score.ObjectType='Member'");
            }
            else
            {
                sbSql.Append(" and score.ObjectType<>'Member'");
            }
            if (!string.IsNullOrEmpty(category))
            {
                sbSql.Append(@" and score.Category=?Category");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = category });
            }

            object obj = MySqlHelper.ExecuteScalar(sbSql.ToString(), list.ToArray());
            return obj != null ? Convert.ToString(obj) : "";
        }

        public static DataTable GetScoreDetail(string objectID, string category, string objectType, int pageIndex, int pageSize)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>()
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize},
                new MySqlParameter() {DbType = DbType.String, ParameterName = "ObjectID", Value = objectID}
            };
            sbSql.Append(@"SELECT `Name`,score.Score,ScoreDate,score.Description FROM tblPartyObjectScore score
inner JOIN tblpartyobjectscorerule rule ON rule.Category=score.Category
WHERE ObjectID=?ObjectID");

            if (objectType.Equals("Member"))
            {
                sbSql.Append(" and score.ObjectType='Member'");
            }
            else
            {
                sbSql.Append(" and score.ObjectType<>'Member'");
            }
            if (!string.IsNullOrEmpty(category))
            {
                sbSql.Append(" and score.Category=?Category");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = category });
            }
            sbSql.Append(" ORDER BY ScoreDate DESC LIMIT ?Begin,?PageSize;");

            return MySqlHelper.ExecuteDataTable(sbSql.ToString(), list.ToArray());
        }

        public static string GetStarClass(decimal score, int starCount = 29)
        {
            int count = Convert.ToInt32(score) / 100;
            if (count <= starCount)
            {
                return GetStarClass(score, true, starCount);
            }
            return GetStarHtml(StringHelper.NewID, score, starCount);
        }

        public static string GetStarClass(decimal score, bool isAll, int starCount = 29)
        {
            /*
            等级规则
            1颗星 = 100积分
            1个月亮 = 10颗星星
            1个太阳 = 10个月亮
             */
            StringBuilder sbStar = new StringBuilder();
            string sun = "<span class=\"party-score-sun-icon\"></span>";
            string mon = "<span class=\"party-score-mon-icon\"></span>";
            string start = "<span class=\"party-score-star-icon\"></span>";

            int sunCount = Convert.ToInt32(score) / 10000;
            int monCount = 0;
            int startCount = 0;

            if (sunCount > 0)
                monCount = (Convert.ToInt32(score) % 10000) / 1000;
            else
                monCount = Convert.ToInt32(score) / 1000;

            if (monCount > 0)
                startCount = ((Convert.ToInt32(score) % 10000) % 1000) / 100;
            else
                startCount = Convert.ToInt32(score) / 100;

            for (int i = 0; i < sunCount; i++)
            {
                sbStar.Append(sun);
            }

            for (int i = 0; i < monCount; i++)
            {
                sbStar.Append(mon);
            }

            for (int i = 0; i < startCount; i++)
            {
                sbStar.Append(start);
            }

            //int count = Convert.ToInt32(score) / 100;
            //if (!isAll && count > starCount)
            //{
            //    count = starCount;
            //}
            //for (int i = 0; i <= count; i++)
            //{
            //    sbStar.Append("<span class=\"party-score-star-icon\"></span>");
            //}
            return sbStar.ToString();
        }

        private static string GetStarHtml(string id, decimal score, int starCount = 29)
        {
            string html = "<div class=\"starDiv\" id=\"all{0}\" style=\"display:none;\">{1}<span class=\"starOP\" onclick=\"StarOp('{0}','');\">[收起]</span></div>" +
"<div class=\"starDiv\" id=\"{0}\">{2}…<span class=\"starOP\" onclick=\"StarOp('{0}','all');\">[展开]</span></div>";
            return string.Format(html, id, GetStarClass(score, true), GetStarClass(score, false, starCount));

        }
    }

    public class AddForForeignIdPara
    {
        /// <summary>
        /// 获得积分的人/组织ID
        /// </summary>
        public string ObjectID { get; set; }
        /// <summary>
        /// 积分标识
        /// </summary>
        public string Category { get; set; }
        /// <summary>
        /// 关联ID(关联产生积分的业务ID)
        /// </summary>
        public string ForeignID { get; set; }
    }

    /// <summary>
    /// 积分信息
    /// </summary>
    public class ScoreInfo
    {
        public ScoreInfo()
        {
            ObjectID = "";
            CreateBy = "";
            ForeignID = "";
            Flag = "";
            Description = "";
            ScoreDate = "0";
            Category = "";
        }

        /// <summary>
        /// 获得积分的人/组织ID
        /// </summary>
        public string ObjectID { get; set; }

        /// <summary>
        /// member人员，branch组织
        /// </summary>
        public SysEnum.PartyScoreObjectType ObjectType { get; set; }

        /// <summary>
        /// 积分标识
        /// </summary>
        public string Category { get; set; }

        /// <summary>
        /// 创建人
        /// </summary>
        public string CreateBy { get; set; }

        /// <summary>
        /// 关联ID(关联产生积分的业务ID)
        /// </summary>
        public string ForeignID { get; set; }

        /// <summary>
        /// 扩展标识
        /// </summary>
        public string Flag { get; set; }

        /// <summary>
        /// 积分描述
        /// </summary>
        public string Description { get; set; }

        /// <summary>
        /// 积分时间（不设置时为当前时间）
        /// </summary>
        public string ScoreDate { get; set; }

    }
}
